/*
 * Copyright (C) 2009 Dimitrios C. Michalakos
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package com.restsql.db;

import com.restsql.db.io.DataIO;
import com.restsql.db.io.MySQLDataIO;
import com.restsql.db.sql.DerbyDesigner;
import com.restsql.db.sql.MySQLDesigner;
import com.restsql.db.sql.SQLDesigner;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

/**
 * Database object can be used to easily get or set io from a designated database.
 * The term database in this case refers to six (6) attributes:
 * 1. type, i.e. MySQL
 * 2. host, i.e. 167.200.187.200 or my-server.com
 * 3. port, i.e. 3306
 * 4. schema, i.e. logistics
 * 5. username
 * 6. password
 * @author dimitris@jmike.gr
 * @author jiannan.lu
 */
public class Database {

    private final Connection conn;
    private final DatabaseType type;
    private final String schema;
    private TableInfo tableInfo = null;
    private Map<String, ColumnInfo> columnInfo = new HashMap<String, ColumnInfo>();
    private ForeignKeyInfo foreignKeyInfo = null;

    /**
     * Creates a new Database object and connects to the specified database.
     * @param type The type of the database (i.e. MYSQL or MSSQL or Derby).
     * @param host The host of the database.
     * @param port The port of the database.
     * @param schema The schema of the database.
     * @param username The username for connecting to the database.
     * @param password The password for connecting to the database.
     * @throws java.sql.SQLException
     */
    public Database(DatabaseType type, String host, int port, String schema, String username, String password) throws SQLException {
        try {
            this.conn = ConnectionFactory.getConnection(type, host, port, schema, username, password);
            System.out.println(conn.getMetaData().getDatabaseProductName()+":"+conn.getMetaData().getURL()+"=>connected");
            //String myURL = conn.getMetaData().getURL();
            this.type = type;
            this.schema = schema;
        } catch (ClassNotFoundException ex) {
            final SQLExceptionState state = SQLExceptionState.DATABASE_NOT_CONNECTED;
            throw new SQLException(ex.getMessage(), state.name(), state.code());
        }
    }

    public Database(String className,String connStr,String userName,String pwd) throws SQLException {
        try {
            this.conn = ConnectionFactory.getConnection(className,connStr,userName,pwd);
            System.out.println(conn.getMetaData().getDatabaseProductName()+":"+conn.getMetaData().getURL()+"=>connected");
            className = className.toUpperCase();
            if (className.indexOf("MYSQL")>0) {
                this.type = DatabaseType.MYSQL;

            } else if (className.indexOf("MsSQL")>0) {
                this.type = DatabaseType.MSSQL;

            } else if (className.indexOf("DERBY")>0) {//jdbc:derby:derbyDB
                this.type = DatabaseType.DERBY;
            } else {
                this.type = DatabaseType.UNKNOWN;
            }
            schema = getConnectionDatabaseName();
        } catch (ClassNotFoundException ex) {
            final SQLExceptionState state = SQLExceptionState.DATABASE_NOT_CONNECTED;
            throw new SQLException(ex.getMessage(), state.name(), state.code());
        }
    }

    /**
     * Closes connection to database.
     * If connection is already closed it does nothing.
     * @throws java.sql.SQLException
     */
    public void close() throws SQLException {
        if (conn != null) {//connection not null, thus a connection to database has been opened
            conn.close();
            tableInfo = null;
            columnInfo.clear();
            foreignKeyInfo = null;
        }
    }

    /**
     * Returns database schema name.
     * @return
     */
    public String getSchemaName() {
        String str_schema = schema;
        if (this.type == DatabaseType.DERBY) {
            str_schema = schema.equals("") ? "APP" : schema;
        }
        return str_schema;
    }

    /**
     * Returns database type.
     * @return
     */
    public DatabaseType getType() {
        return type;
    }

    /**
     * Retrieves a DatabaseMetaData object that contains metadata about the database.
     * @return DatabaseMetaData object.
     * @throws java.sql.SQLException
     */
    public DatabaseMetaData getDatabaseMetaData() throws SQLException {
        return conn.getMetaData();
    }

    /**
     * Creates a Statement object for sending SQL statements to the database.
     * @return a new default Statement object
     * @throws java.sql.SQLException
     */
    public Statement createStatement() throws SQLException {
        return conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);//Create statement
    }

    /**
     * Creates a CallableStatement object for calling database stored procedures..
     * @param resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE.
     * @param resultSetConcurrency one of the following ResultSet constants: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE.
     * @return new Statement object that will generate ResultSet objects with the given type and concurrency
     * @throws java.sql.SQLException
     */
    public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
        return conn.createStatement(resultSetType, resultSetConcurrency);//Create statement
    }

    /**
     * Creates a CallableStatement object for calling database stored procedures.
     * @param sql A SQL statement that may contain one or more '?' parameter placeholders.
     * @return a new CallableStatement object containing the pre-compiled SQL statement.
     * @throws java.sql.SQLException
     */
    public CallableStatement getCallableStatement(String sql) throws SQLException {
        return conn.prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);//Create callable statement
    }

    /**
     * Creates a CallableStatement object for calling database stored procedures.
     * @param sql A SQL statement that may contain one or more '?' parameter placeholders.
     * @param resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE.
     * @param resultSetConcurrency one of the following ResultSet constants: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE.
     * @return a new CallableStatement object containing the pre-compiled SQL statement.
     * @throws java.sql.SQLException
     */
    public CallableStatement getCallableStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return conn.prepareCall(sql, resultSetType, resultSetConcurrency);//Create callable statement
    }

    /**
     * Creates a PreparedStatement object for sending parameterized SQL statements to the database.
     * @param sql A SQL statement that may contain one or more '?' IN parameter placeholders.
     * @return A new PreparedStatement object containing the pre-compiled SQL statement.
     * @throws java.sql.SQLException
     */
    public PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);//Create prepared statement
    }

    /**
     * Creates a PreparedStatement object for sending parameterized SQL statements to the database.
     * @param sql A SQL statement that may contain one or more '?' IN parameter placeholders.
     * @param resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE.
     * @param resultSetConcurrency one of the following ResultSet constants: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE.
     * @return A new PreparedStatement object containing the pre-compiled SQL statement.
     * @throws java.sql.SQLException
     */
    public PreparedStatement getPreparedStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return conn.prepareStatement(sql, resultSetType, resultSetConcurrency);//Create prepared statement
    }

    /**
     * Executes the given SQL statement and returns a single ResultSet object.
     * @param sql A SQL statement.
     * @return A new Resultset object.
     * @throws java.sql.SQLException
     */
    public ResultSet getResultSet(String sql) throws SQLException {
        return this.getResultSet(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    }

    /**
     * Executes the given SQL statement and returns a single ResultSet object.
     * @param sql a SQL statement.
     * @param resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE.
     * @param resultSetConcurrency one of the following ResultSet constants: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE.
     * @return a new Resultset object.
     * @throws java.sql.SQLException
     */
    public ResultSet getResultSet(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        Statement stmt = conn.createStatement(resultSetType, resultSetConcurrency);//create statement
        return stmt.executeQuery(sql);//execute sql - get resultset
    }

    /**
     * Executes the given SQL statement and returns a ResultSetMetaData object.
     * @param sql A SQL statement.
     * @return A new ResultSetMetaData object.
     * @throws java.sql.SQLException
     */
    public ResultSetMetaData getResultSetMetaData(String sql) throws SQLException {
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);//create statement
        ResultSet rs = this.getResultSet(sql);
        ResultSetMetaData rsmd = rs.getMetaData();//get ResultSetMetaData from resultset
        rs.close();//close resultset
        stmt.close();//close statement
        return rsmd;//return ResultSetMetaData
    }

    /**
     * Returns a MySQLTableInfo object containing information about the types and properties of the tables in the database.
     * @return A new MySQLTableInfo object.
     * @throws java.sql.SQLException
     */
    public TableInfo getTableInfo() throws SQLException {
        if (this.tableInfo == null) {//tableInfo does not exist in memory
            /* calculate tableInfo */
            switch (this.type) {
                case MYSQL: {
                    this.tableInfo = new MySQLTableInfo(this);
                    break;
                }
                case MSSQL: {
                    /* soon availiable */
                    break;
                }
                case DERBY: {
                    this.tableInfo = new DerbyTableInfo(this);
                    break;
                }
                default: {
                    /* do nothing */
                    break;
                }
            }
        }
        return this.tableInfo;//return tableInfo from memory
    }

    /**
     * Returns a ColumnInfo object containing information about the types and properties of the the designated table's columns.
     * @param table the name of the table.
     * @return a new ColumnInfo object.
     * @throws java.sql.SQLException
     */
    public ColumnInfo getColumnInfo(String table) throws SQLException {
        if (!this.columnInfo.containsKey(table)) {//columnInfo for specified table does not exist in memory
            /* calculate columnInfo */
            switch (this.type) {
                case MYSQL: {
                    this.columnInfo.put(table, new MySQLColumnInfo(this, table));
                    break;
                }
                case MSSQL: {
                    /* soon availiable */
                    break;
                }
                case DERBY: {
                    this.columnInfo.put(table, new DerbyColumnInfo(this, table));
                    break;
                }
                default: {
                    /* do nothing */
                    break;
                }
            }
        }
        return columnInfo.get(table);
    }

    /**
     * Returns a ColumnInfo object containing information about the types and properties of the designated resutlset's columns.
     * @param rs the resultset.
     * @return a new ColumnInfo object.
     * @throws java.sql.SQLException
     */
    public ColumnInfo getColumnInfo(ResultSet rs) throws SQLException {
        final String key = "_resultset." + String.valueOf(rs.hashCode());
        if (!this.columnInfo.containsKey(key)) {//columnInfo for specified table does not exist in memory
            /* calculate columnInfo */
            switch (this.type) {
                case MYSQL: {
                    this.columnInfo.put(key, new MySQLColumnInfo(this, rs));
                    break;
                }
                case MSSQL: {
                    /* soon availiable */
                    break;
                }
                case DERBY: {
                    this.columnInfo.put(key, new DerbyColumnInfo(this, rs));
                    break;
                }
                default: {
                    /* do nothing */
                    break;
                }
            }
        }
        return columnInfo.get(key);
    }

    /**
     * Returns a ForeignKeyInfo object containing information about the referential constraint of this database.
     * @return A new ForeignKeyInfo object.
     * @throws java.sql.SQLException
     */
    public ForeignKeyInfo getForeignKeyInfo() throws SQLException {
        if (this.foreignKeyInfo == null) {//foreignKeyInfo does not exist in memory
            /* calculate foreignKeyInfo */
            switch (this.type) {
                case MYSQL: {
                    this.foreignKeyInfo = new MySQLForeignKeyInfo(this);
                    break;
                }
                case MSSQL: {
                    /* soon availiable */
                    break;
                }
                case DERBY: {
                    this.foreignKeyInfo = new DerbyForeignKeyInfo(this);
                    break;
                }
                default: {
                    /* do nothing */
                    break;
                }
            }
        }
        return this.foreignKeyInfo;
    }

    /**
     * Returns a new DataIO object for this database.
     * @return
     */
    public DataIO getDataIO() {
        switch (this.type) {
            case MYSQL: {
                return new MySQLDataIO(this);
            }
            case MSSQL: {
                return null;
            }
            default: {
                return null;
            }
        }
    }

    /**
     * Returns a new SQLDesigner object for this database.
     * @return
     */
    public SQLDesigner getSQLDesigner() {
        switch (this.type) {
            case MYSQL: {
                return new MySQLDesigner(this);
            }
            case MSSQL: {
                return null;
            }
            case DERBY: {
                return new DerbyDesigner(this);
            }
            default: {
                return null;
            }
        }
    }

    /**
     * Parses the designated URI template and returns a new Action object.
     * @param name the name of the action.
     * @param label the label of the action.
     * @param template the URI template containing variablePos within brackets, i.e. http://www.google.com/q={term}
     * @return
     */
//    public Action getAction(String name, String label, String template) {
//        switch (this.type) {
//            case MYSQL: {
//                return new MySQLAction(this, name, label, template);
//            }
//            case MSSQL: {
//                return null;
//            }
//            default: {
//                return null;
//            }
//        }
//    }
    /**
     * Returns an XHTMLTable representation of the designated ResultSet.
     * @param rs the resultset.
     * @return
     * @throws java.sql.SQLException
     */
//    public XHTMLTable getXHTMLTable(ResultSet rs) throws SQLException {
//        switch (this.type) {
//            case MYSQL: {
//                return new MySQLXHTMLTable(this, rs);
//            }
//            case MSSQL: {
//                return null;
//            }
//            default: {
//                return null;
//            }
//        }
//    }
    /**
     * Returns an XHTMLForm representation of the designated ResultSet.
     * @param rs the resultset.
     * @return
     * @throws java.sql.SQLException
     */
//    public XHTMLForm getXHTMLForm(ResultSet rs) throws SQLException {
//        switch (this.type) {
//            case MYSQL: {
//                return new MySQLXHTMLForm(this, rs);
//            }
//            case MSSQL: {
//                return null;
//            }
//            default: {
//                return null;
//            }
//        }
//    }
    /**
     * Executes the given SQL INSERT statement and returns the generated key of row that was inserted.
     * @param sql
     * @return
     * @throws java.sql.SQLException
     */
    public String setNew_geneKey(String sql) throws SQLException {
        String key = "";
        Statement stmt = conn.createStatement();///createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);//Create statement
        try {
            int rows = stmt.executeUpdate(sql, stmt.RETURN_GENERATED_KEYS);//executeQuery(sql);//Execute sql - set resultset
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                key = rs.getString(1);
            }
            conn.commit();
        } catch (Exception ex) {
            Object ob = ex;
        }
        stmt.close();//Close statement
        return key;//Return key of row that was inserted
    }

     public int setNew(String sql) throws SQLException {
        int rows = 0;
        Statement stmt = conn.createStatement();///createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);//Create statement
        try {
             rows = stmt.executeUpdate(sql);//executeQuery(sql);//Execute sql - set resultset
             conn.commit();
        } catch (Exception ex) {
            Object ob = ex;
        }
        stmt.close();//Close statement
        return rows;//Return key of row that was inserted
    }

    /**
     * Executes the given SQL UPDATE query and returns the number of rows that where updated.
     * @param sql
     * @return
     * @throws java.sql.SQLException
     */
    public int set(String sql) throws SQLException {
        /* Create statement */
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        /* Execute sql - set resultset */
        int count = stmt.executeUpdate(sql);

        conn.commit();
        /* Close statement */
        stmt.close();
        /* Return number of rows that where updated */
        return count;
    }

    /**
     * Executes the given SQL DELETE query and returns the number of rows that where deleted.
     * @param sql
     * @return
     * @throws java.sql.SQLException
     */
    public int delete(String sql) throws SQLException {
        /* Create statement */
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        /* Execute sql - set resultset */
        int count = stmt.executeUpdate(sql);

        conn.commit();
        /* Close statement */
        stmt.close();
        /* Return number of rows that where deleted */
        return count;
    }

     public String getConnectionDatabaseName() throws SQLException {
//        Map<String, String> map = ConfigHelper.getDataSourceConfig();
        String url = this.conn.getMetaData().getURL();
//        String userName = map.get("dataSource.username");
//        StringBuffer urlTemplate = new StringBuffer();
//        urlTemplate.append("oracle=jdbc:oracle:thin:@机器名或IP:1521:SID名称\n\n");
//        urlTemplate.append("db2=jdbc:db2://机器名或IP:5000/数据库名称\n\n");
//        urlTemplate.append("sqlserver=jdbc:microsoft:sqlserver://机器名或IP:1433;DatabaseName=数据库名称\n\n");
//        urlTemplate.append("sybase=jdbc:sybase:Tds:机器名或IP:5007/数据库名称\n\n");
//        urlTemplate.append("informix=jdbc:informix-sqli://123.45.67.89:1533/数据库名称:INFORMIXSERVER=机器名或IP\n\n");
//        urlTemplate.append("mysql=jdbc:mysql://机器名或IP/数据库名称?user=xx&password=xx&useUnicode=true&characterEncoding=8859_1\n\n");
//        urlTemplate.append("postgresql=jdbc:postgresql://机器名或IP/数据库名称\n\n");
//        urlTemplate.append("Access=jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=application.getRealPath(\"/路径/access文件\")\n\n");
//        if (url.toLowerCase().indexOf("jdbc:db2:") > -1)//com.ibm.db2.jdbc.app.DB2Driver
//        {
//            return url.substring(url.lastIndexOf("/") + 1).trim();
//        } else if (url.toLowerCase().indexOf("jdbc:microsoft:") > -1)//com.microsoft.jdbc.sqlserver.SQLServerDriver
//        {
//            return url.substring(url.lastIndexOf("=") + 1).trim();
//        } else if (url.toLowerCase().indexOf("jdbc:sybase:") > -1)//com.sybase.jdbc.SybDriver
//        {
//            return url.substring(url.lastIndexOf("/") + 1).trim();
//        } else if (url.toLowerCase().indexOf("jdbc:informix-sqli:") > -1)//com.informix.jdbc.IfxDriver
//        {
//            return url.substring(url.lastIndexOf("/") + 1, url.lastIndexOf(":")).trim();
//        } else
        if (url.toLowerCase().indexOf("jdbc:mysql:") > -1)//org.gjt.mm.mysql.Driver
        {
            return url.substring(url.lastIndexOf("/") + 1, (url.lastIndexOf("?")>0?url.lastIndexOf("?"):(url.length()))).trim();
        }

            else {
          //  Log4JUtil.error("getCurrentDatabaseName()错误：\n您定义的url:\"" + url + "\"与本方法使用的格式不匹配，能否使用如下格式：\n" + urlTemplate.toString());
            return "";
        }
    }
}

